<?php
/**
 * 成績統計
 * @author cik
 *
 */
class Score_Model_Statistics
{
    /**
     * 顯示全學期成績
     * @var integer
     */
    const WHOLE_STAGE_SCORE = 255;
    /**
     *  全學年排名模式
     * @var integer
     */
    const TOP_GRADE_MODEL = 1;
    /**
     *  全班排名模式
     * @var integer
     */
    const TOP_CLASS_MODEL = 2;

    protected $_year;
    protected $_semester;
    protected $_grade;
    protected $_classSn;
    protected $_examine;
    protected $_subject;
    protected $_roundNum;

    /**
     *
     * @param string $classNum 班級
     * @param string $yearSeme 學年學期
     */
    public function __construct($classNum, $yearSeme=NULL, $stage=NULL)
    {
        // 取系統預設值
        $param = new Default_Model_ModulesParam();
        $param_arr = $param->getParamByResourceId('score');
        // 小數計算位數
        $this->_roundNum = $param_arr['round_number'] ;

        $this->setClassNum($classNum, $yearSeme);

        // 設定學科
        if ($stage)
        $this->_setSubject($stage);

    }

    public function setClassNum($classNum,$yearSeme=NULL)
    {
        if ($yearSeme==NULL) {
            $cache = Zend_Registry::get('cache');
            $tempYearSeme = $cache->load('semester');
            $yearSeme = $tempYearSeme['yearSeme'];
        }

        $this->_setYearSeme($yearSeme);

        $temp = explode("-",$classNum);
        $this->_grade = (int)$temp[0];
        if (count($temp) >1)
        $this->_classSn = (int)$temp[1];
        $this->_setStage();

    }

    public function getClassName()
    {
        // 班級陣列
        $semesterClass = new Semester_Model_SemesterClass;
        $classArr = $semesterClass->getClassNames($this->_year,$this->_semester);
        $classNum = $this->_grade.'-'.$this->_classSn;
        return $classArr[$classNum];
    }

    /**
     * 設定學年學期
     * @param $yearSeme
     * @return unknown_type
     */
    protected  function _setYearSeme($yearSeme)
    {
        $temp = explode("-",$yearSeme);
        $this->_year = (int) $temp[0];
        $this->_semester = (int)$temp[1];
    }

    /**
     * 取出目前學習考試階段數
     * @return unknown_type
     */
    protected function _setStage()
    {
        //查詢該學科考次,定期平時比率
        $examine = new Semester_Model_SemesterExamine();
        $this->_examine = $examine->getExaminSet($this->_year,
        $this->_semester, $this->_grade);

    }

    /**
     * 取出學科名稱
     * @param unknown_type $stage
     */
    protected  function _setSubject($stage)
    {
        $semester = new Semester_Model_SemesterSubject();
        if ($stage != self::WHOLE_STAGE_SCORE)
        $arr = $semester->getSubject($this->_year,$this->_semester,$this->_grade,
        $this->_classSn, Semester_Model_SemesterSubject::SCORE_MODE_STAGE ,
        Semester_Model_SemesterSubject::SCORE_MODE_SCORED  );
        else
        $arr = $semester->getSubject($this->_year,$this->_semester,$this->_grade,
        $this->_classSn,null, Semester_Model_SemesterSubject::SCORE_MODE_SCORED );
        $this->_subject = $arr;

        return $arr;
    }
    /**
     *  成績設定
     * @return unknown_type
     */
    public function getExamineSetup()
    {
        return $this->_examine;
    }

    /**
     * 科目列表
     * @param integer $stage
     * @return Doctrine_Record
     */
    public function getSubject()
    {
        if ($this->_subject)
        return $this->_subject;
        else return false;
    }

    public function getGrade()
    {
        return $this->_grade;
    }

    public function getClassSn()
    {
        return $this->_classSn;
    }

    /**
     *  階段成績平均
     * @param integer $stage 階段別
     * @param array $subjectArr 計算科目
     * @param integer $plusCount 加計平時成績
     */
    public function getAverageScore($stage, $subjectArr=array(), $plusCount=0)
    {
        $subjects = array_keys($subjectArr);
        $q = Doctrine_Query::create()
        ->select('a.sn, avg(a.score) AS avg , count(*) AS cc,
             b.class_sn AS class_sn , b.test_kind AS test_kind,
             b.test_sort AS test_sort, b.subject_sn AS subject_sn')
        ->from('Student_Model_StudentScoreStageDetail a')
        ->leftJoin('a.Student_Model_StudentScoreStage b')
        ->whereIn('b.subject_sn', $subjects);
        if ($stage != self::WHOLE_STAGE_SCORE)
        $q->andWhere('b.test_sort=?',$stage);

        $q->groupBy('class_sn, b.subject_sn, b.test_kind, b.test_sort');

        $res = $q->fetchArray();

        $ratio = $this->_examine->ratio;


        $arr = array();
        foreach ($res as $row) {
            if (!isSet($arr[$row['class_sn']][$row['subject_sn']]))
            $arr[$row['class_sn']][$row['subject_sn']]['avg'] = 0 ;

            $arr[$row['class_sn']][$row['subject_sn']]['count']  = $row['cc'];
            // 全學期打一次成績
            if ($stage==self::WHOLE_STAGE_SCORE and $row['test_sort'] == self::WHOLE_STAGE_SCORE)
            $arr[$row['class_sn']][$row['subject_sn']]['avg']  = $row['avg'];
            // 加計平時成績
            elseif ($plusCount) {
                // 全學期成績,計算所有階段的成績
                if ($stage == self::WHOLE_STAGE_SCORE)
                $arr[$row['class_sn']][$row['subject_sn']]['avg'] +=
                $row['avg'] * ($ratio[$row['test_sort']][$row['test_kind']]/$ratio['all']);
                else
                $arr[$row['class_sn']][$row['subject_sn']] ['avg']+=
                $row['avg'] * ($ratio[$row['test_sort']][$row['test_kind']]/100);
            }
            else {
                if ($row['test_kind'] == Semester_Model_SemesterExamine::REGULAR_SCORE)
                $arr[$row['class_sn']][$row['subject_sn']] ['avg'] = $row['avg'];
            }
        }
        $tempArr =  $arr;
        $tempSubjectRank = array(); // 排序用
        $tempClassRank = array();

        foreach ($tempArr as $classSn=>$val) {
            foreach ($val as $subject_sn =>$row) {
                if (!isset( $arr[$classSn]['total']))
                $arr[$classSn]['total'] = 0;
                $arr[$classSn][$subject_sn]['avg'] = round($row['avg'], $this->_roundNum);
                $tempRank[$subject_sn][]  = $row['avg'];
                $arr[$classSn]['total'] += $arr[$classSn][$subject_sn]['avg'];
            }
            $tempClassRank[] = $arr[$classSn]['total'] ;
        }


        // 計算科目排名
         foreach ($tempArr as $classSn=>$val) {
            foreach ($val as $subject_sn =>$row) {
                      $arr[$classSn][$subject_sn]['rank'] = $this->_sortArr($row['avg'], $tempRank[$subject_sn]);
            }
            $arr[$classSn]['rank'] = $this->_sortArr( $arr[$classSn]['total'],$tempClassRank);
         }
        return $arr;
    }

    /**
     *  階段成績排名
     * @param integer $stage 階段別
     * @param integer $listKind 統計方式
     * @param array $subjectArr 計算科目
     * @param integer $plusCount 加計平時成績
     * @param integer $rateCount 加權計算
     * @param integer $topNumber 排名數
     */
    public function getTopScore($stage, $listKind, $subjectArr=array(), $plusCount=0, $rateCount=0, $topNumber=50)
    {
        $arr = array();
        // 建立 暫存 Table
        $dh = Doctrine_Manager::getInstance()->getCurrentConnection();
        $createTable="
            CREATE temporary TABLE  temp_score_top (
            sn int(10) unsigned NOT NULL  auto_increment,
            student_sn int(10) unsigned NOT NULL default '0',
            class_sn int(4) NOT NULL default '0',
            score float NOT NULL default '0',
            subject_arr text ,
            PRIMARY KEY  (sn))
            ";
        $dh->execute($createTable) or die($createTable);

        $ratio = $this->_examine->ratio;
        $subjects = array_keys($subjectArr);
        //        print_r($ratio);
        $q = Doctrine_Query::create()
        ->select('a.score, a.student_sn, b.test_kind AS test_kind, b.test_sort AS test_sort,
            b.subject_sn AS subject_sn, b.class_sn AS class_sn')
        ->from('Student_Model_StudentScoreStageDetail a')
        ->leftJoin('a.Student_Model_StudentScoreStage b')
        ->whereIn('b.subject_sn', $subjects);
        if ($stage != self::WHOLE_STAGE_SCORE)
        $q->andWhere('b.test_sort=?',$stage);

        $res = $q->fetchArray();
        foreach ($res as $row) {
            if (!isSet($arr[$row['class_sn']][$row['student_sn']][$row['subject_sn']]))
            $arr[$row['class_sn']][$row['student_sn']][$row['subject_sn']] = 0 ;

            // 全學期打一次成績
            if ($stage==self::WHOLE_STAGE_SCORE and $row['test_sort'] == self::WHOLE_STAGE_SCORE)
            $arr[$row['class_sn']][$row['student_sn']][$row['subject_sn']] = $row['score'];
            // 加計平時成績
            elseif ($plusCount) {
                // 全學期成績,計算所有階段的成績
                if ($stage == self::WHOLE_STAGE_SCORE)
                $arr[$row['class_sn']][$row['student_sn']][$row['subject_sn']] +=
                $row['score'] * ($ratio[$row['test_sort']][$row['test_kind']]/$ratio['all']);
                else
                $arr[$row['class_sn']][$row['student_sn']][$row['subject_sn']] +=
                $row['score'] * ($ratio[$row['test_sort']][$row['test_kind']]/100);
            }
            else {
                if ($row['test_kind'] == Semester_Model_SemesterExamine::REGULAR_SCORE)
                $arr[$row['class_sn']][$row['student_sn']][$row['subject_sn']] = $row['score'];
            }

        }

        // 科目加權
        if ($rateCount) {
            $tempArr = $this->_setSubject($stage);
            $subjectRate = array();
            $totalRate = 0;
            foreach($tempArr as $row ) {
                foreach ($row as $val) {
                    $subjectRate[$val['subject_sn']] = $val['rate'];
                    $totalRate += $val['rate'];
                }
            }
        }

        foreach ($arr as $classSn=>$val) {
            foreach($val as $studentSn=>$val2) {
                $total = 0;
                $subjectTemp = array();
                foreach($val2 as $subjectSn=>$score){
                    $subjectTemp[$subjectSn] = $score;
                    // 加權計算
                    if ($rateCount)
                    $score= $score * $subjectRate[$subjectSn];

                    $total += $score;
                }
                // 新增至資料
                $query ="INSERT INTO temp_score_top (student_sn,class_sn,score,subject_arr)
                 VALUES($studentSn, $classSn, $total,'".serialize($subjectTemp)."')";
                $dh->Execute($query) or die($query);
            }
        }


        $query = "SELECT a.*, b.name, c.student_id, c.number FROM temp_score_top a
        LEFT JOIN student b ON a.student_sn=b.sn LEFT JOIN student_semester c
        ON a.student_sn=c.student_sn WHERE c.year=".$this->_year." AND c.semester=".$this->_semester;
        if ($listKind == self::TOP_GRADE_MODEL or $topNumber == 0)
        $query .= "  ORDER BY a.score DESC ";
        else
        $query .= "  ORDER BY a.class_sn, a.score DESC ";

        $res = $dh->Execute($query)->fetchAll();

        if (count($res)==0) return array();

        // 如果排名為 0 時 則回傳所有資料
        if ($topNumber == 0 )
        return $res;

        $prevScore = $res[0]['score'];
        // 目前筆數
        $currentNumber = 0;
        // 重覆筆數
        $doubleCount = 0;
        $i = 0;
        $arr = array();
        $cc = count($res);
        if ($listKind == self::TOP_GRADE_MODEL) { // 全學年排名
            foreach ($res as $row) {
                //中斷判別:  如果多於排名數 且 下一筆分數不相等時
                if ($i  >= $topNumber   and count($arr) < $cc and  (isset($res[count($arr)+1]) and $res[count($arr)+1]['score'] != $row['score']))
                break;
                // 如果和前一筆分數 重複
                if ($i++ > 0 and $prevScore == $row['score'])
                $doubleCount ++;
                else
                $doubleCount = 0 ;

                $currentNumber ++ ;
                $row['rate'] = $currentNumber - $doubleCount;

                $prevScore = $row['score'];
                $arr[] = $row;
            }
        }
        else { // 班級排名
            $classArr = $arr2 = array();
            foreach ($res as $row)
            $classArr[$row['class_sn']][] = $row;
            foreach ($classArr as $classSn=>$val) {
                $i = 0;
                $cc = count($val);
                $prevScore = $val[0]['score'];
                // 目前筆數
                $currentNumber = 0;
                // 重覆筆數
                $doubleCount = 0;
                foreach ($val as $row) {
                    //中斷判別:  如果多於排名數 且 下一筆分數不相等時
                    if ($i  >= $topNumber   and count($arr2[$classSn]) < $cc and  $res[count($arr2[$classSn])+1]['score'] != $row['score'])
                    break;
                    // 如果和前一筆分數 重複
                    if ($i++ > 0 and $prevScore == $row['score'])
                    $doubleCount ++;
                    else
                    $doubleCount = 0 ;

                    $currentNumber ++ ;
                    $row['rate'] = $currentNumber - $doubleCount;

                    $prevScore = $row['score'];
                    $arr2[$classSn][] = $row;
                }
            }
            foreach ($arr2 as $val)
            foreach ($val as $row)
            $arr[] = $row;

        }
        return $arr;

    }

    /**
     * 階段學生成績
     * @param integer $stage 階段
     * @param integer $plusCount 加計平時成績
     * @param integer $rateCount 加權計算
     * @return array
     */
    public function getStudentScore($stage, $plusCount=0, $rateCount=0)
    {
        // 課程設定
        $subjectArr = $this->_setSubject($stage);
        // 階段成績
        $q = Doctrine_Query::create()
        ->select('a.*,c.*')
        ->from('Student_Model_StudentScoreStage a')
        ->leftJoin('a.Student_Model_StudentScoreStageDetail c')
        ->leftJoin('Semester_Model_SemesterSubject b')
        ->where('a.subject_sn=b.subject_sn')
        ->andWhere('a.class_sn=b.class_sn')
        ->andWhere('b.year=?',$this->_year)
        ->andWhere('b.semester=?',$this->_semester)
        ->andWhere('b.grade=?',$this->_grade)
        ->andWhere('b.class_sn=?',$this->_classSn)
        ->addWhere('b.scored=?','Y');
        if ($stage != self::WHOLE_STAGE_SCORE)
        $q->andWhere('a.test_sort=?',$stage);
        //echo $this->_year.'--'. $this->_semester.'--'. $this->_grade.'--'. $this->_classSn.'--'.$stage.'--'.self::WHOLE_STAGE_SCORE;
        //echo $q->getSqlQuery();
        $res = $q->fetchArray();
        if (count($res) == 0)
        return false;
        //   throw (new Exception('Statistics Error: No Data '));
        $ratio = $this->_examine->ratio;
        $arr = array();
        foreach ($res as $row) {
            foreach ($row['Student_Model_StudentScoreStageDetail'] as $val) {
                if ( ! isset ($arr[$val['student_sn']][$row['subject_sn']]))
                $arr[$val['student_sn']][$row['subject_sn']] = 0;

                // 全學期打一次成績
                if ($stage==self::WHOLE_STAGE_SCORE and $row['test_sort'] == self::WHOLE_STAGE_SCORE)
                $arr[$val['student_sn']][$row['subject_sn']] = $val['score'];
                // 加計平時成績
                elseif ($plusCount) {
                    // 全學期成績,計算所有階段的成績
                    if ($stage == self::WHOLE_STAGE_SCORE)
                    $arr[$val['student_sn']][$row['subject_sn']] +=
                    $val['score'] * ($ratio[$row['test_sort']][$row['test_kind']]/$ratio['all']);
                    else
                    $arr[$val['student_sn']][$row['subject_sn']] +=
                    $val['score'] * ($ratio[$row['test_sort']][$row['test_kind']]/100);
                }
                else {
                    if ($row['test_kind'] == Semester_Model_SemesterExamine::REGULAR_SCORE)
                    $arr[$val['student_sn']][$row['subject_sn']] = $val['score'];
                }
            }
        }

        // 科目加權陣列
        $subjectRate = array();
        $rateTotal = 0;
        $subjectCount = 0;
        // 科目成績
        $subjectTotalArr = array();

        $scoreArr = array();
        // 做為排序用
        $sortTotal = array();
        foreach($subjectArr as $row ) {
            foreach ($row as $val) {
                $subjectRate[$val['subject_sn']] = $val['rate'];
                $rateTotal += $val['rate'];
                $subjectCount++;
            }
        }
        // 計算總分平均
        foreach ($arr as $sn=>$val) {
            $total = 0;
            $average = 0;
            foreach ($val as $subjectSn=>$score) {
                //  $score = round($score,$this->_roundNum);
                // 加權計算
                if ($rateCount)
                $total += $score * $subjectRate[$subjectSn];
                else
                $total += $score;

                $subjectTotalArr[$subjectSn][] = $score;

                $val[$subjectSn] = $score;
            }
            $total =  round($total, $this->_roundNum);
            $scoreArr[$sn]['score'] = $val;
            $scoreArr[$sn]['total'] = $total;
            $sortTotal[] = $total;
            if ($rateCount)
            $scoreArr[$sn]['average'] = round($total / $rateTotal, $this->_roundNum) ;
            else
            $scoreArr[$sn]['average'] = round($total /$subjectCount, $this->_roundNum) ;
        }




        $student = new Student_Model_Student();
        $students = $student->getClassStudent($this->_year, $this->_semester,
        $this->_grade, $this->_classSn);
        // 學生陣列
        $studentArr = array();
        $i=0;
        foreach ($students as $val) {
            $val['score'] =   $val['total'] = $val['sort'] = $val['average'] = null;

            if (isset($scoreArr[$val['sn']]['score']))
            $val['score'] = $scoreArr[$val['sn']]['score'];


            if (isset($scoreArr[$val['sn']]['total'])) {
                $val['total'] = $scoreArr[$val['sn']]['total'];
                $val['sort']  = $this->_sortArr($scoreArr[$val['sn']]['total'], $sortTotal);
            }
            if (isset( $scoreArr[$val['sn']]['average']))
            $val['average'] = $scoreArr[$val['sn']]['average'];
            $studentArr[$val['sn']] = $val;
            $i++;
        }
        // 計算總分及標準差
        $subjectStdArr = array();
        $subjectTotaltemp = array();
        foreach ($subjectTotalArr as $sn=>$val) {
            $subjectTotaltemp[$sn] = round(array_sum($val)/$i,$this->_roundNum);
            $subjectStdArr[$sn] = number_format($this->_stbArr($val),2);
        }
        // 成績分布
        $scoreStatistics = $this->_scoreStatistics($subjectTotalArr);
        return array('score'=>$studentArr,
                                'subjectAverage' =>$subjectTotaltemp,
                                'subjectStd'=>$subjectStdArr,
                                'statistics' => $scoreStatistics);
    }

    /**
     * 成績分布情形
     * @param array $arr
     * @return array
     */
    protected function _scoreStatistics($arr)
    {
        $tempArr= array();
        foreach($arr as $sn=>$row) {
            $tempArr['100'][$sn] = 0;
            $tempArr['90-99'][$sn] = 0;
            $tempArr['80-90'][$sn] = 0;
            $tempArr['70-80'][$sn] = 0;
            $tempArr['60-70'][$sn] = 0;
            $tempArr['50-60'][$sn] = 0;
            $tempArr['40-50'][$sn] = 0;
            $tempArr['30-40'][$sn] = 0;
            $tempArr['20-30'][$sn] = 0;
            $tempArr['10-20'][$sn] = 0;
            $tempArr['0-10'][$sn] = 0;
        }
        foreach($arr as $sn=>$row) {
            foreach($row as $val) {
                if ($val == 100) $tempArr['100'][$sn] = $tempArr['100'][$sn] +1;
                elseif($val>=90)  $tempArr['90-99'][$sn] = $tempArr['90-99'][$sn] +1;
                elseif($val>=80)  $tempArr['80-90'][$sn] = $tempArr['80-90'][$sn] +1;
                elseif($val>=70)  $tempArr['70-80'][$sn]  = $tempArr['70-80'][$sn] +1;
                elseif($val>=60)  $tempArr['60-70'][$sn] = $tempArr['60-70'][$sn] +1;
                elseif($val>=50)   $tempArr['50-60'][$sn]  = $tempArr['50-60'][$sn] +1;
                elseif($val>=40)  $tempArr['40-50'][$sn] = $tempArr['40-50'][$sn] +1;
                elseif($val>=30)  $tempArr['30-40'][$sn]  = $tempArr['30-40'][$sn] +1;
                elseif($val>=20)  $tempArr['20-30'][$sn] = $tempArr['20-30'][$sn] +1;
                elseif($val>=10)  $tempArr['10-20'][$sn] =$tempArr['10-20'][$sn] +1;
                else $tempArr['0-10'][$sn] = $tempArr['0-10'][$sn] +1;
            }
        }
        return $tempArr;
    }


    /**
     * 計算Array標準差函式
     * @param $sDev
     * @return float
     */
    protected function _stbArr ($sDev){
        //先算出平均值
        $avg=array_sum($sDev)/count($sDev);
        $ds=array();
        $i=0;
        while(list($k,$v)=each($sDev)){
            $d=($v-$avg);
            $ds[$i]=pow($d,2);
            $i++;
        }
        $sqrt=sqrt((array_sum($ds)/count($ds)));
        return $sqrt;
    }

    /**
     * 算出這個值是陣列中第幾大的，a是一個數，b是一個陣列
     */
    protected function  _sortArr($a,$b){
        $sort=1;
        while(list($id,$val)=each($b)){
            if($a<$val) $sort++;
        }
        return  $sort;
    }
}